package csci3170;

import java.io.*;
import java.text.SimpleDateFormat;
import java.text.ParseException;
import java.util.Date;
import java.sql.*;

public class SampleDataReader {
    private Connection con;
   // private Statement stmt ;
    private PreparedStatement pstmt;
    /**
     * Class representing a record of Course in the DB
     *
     */

    public SampleDataReader(){
            
    }
    public static class Course {

            /**
             * The 4 attributes of a record
             *
             */
            public String id;
            public String name;
            public long credit;
            public String department;

            /**
             * Constructor of the Course Class.
             *
             */
            public Course() {
            }

            /**
             * Print out all the members for debug.
             *
             */
            public String toString() {
                    return (id + "\t" + name + "\t" + credit + "\t" + department);
            }
    };

    /**
     * Class representing a record of TermCourse in the DB
     *
     */
    public static class TermCourse {

            /**
             * The 6 attributes of a record
             *
             */
            public String id;
            public long year;
            public long term;
            public String prof;
            public long quota;
            public long numberOfEnrolledStudent;

            /**
             * Constructor of the TermCourse Class.
             *
             */
            public TermCourse() {
            }

            /**
             * Print out all the members for debug.
             *
             */
            public String toString() {
                    return (id + "\t" + year + "\t" + term + "\t" + prof + "\t" + quota + "\t" + numberOfEnrolledStudent);
            }
    }

    public static class Prerequisite {
            /**
             * The 2 attributes of a record
             *
             */
            public String cid;
            public String pid;

            /**
             * Constructor of the Prerequisite Class.
             *
             */
            public Prerequisite() {
            }

            /**
             * Print out all the members for debug.
             *
             */
            public String toString() {
                    return (cid + "\t" + pid);
            }
    }

    /**
     * Class representing a record of Student in the DB
     *
     */
    public static class Student {

            /**
             * The 9 attributes of a record
             *
             */
            public String id;
            public String name;
            public String gender;
            public String address;
            public String major;
            public String college;
            public long dateOfBirth;//represented in the number of milliseconds since January 1, 1970, 00:00:00 GMT.
            public String phone;
            public long admissionYear;

            /**
             * Constructor of the Student Class.
             *
             */
            public Student() {
            }

            /**
             * Print out all the members for debug.
             *
             */
            public String toString() {
                    return (id + "\t" + name + "\t" + gender + "\t" + address + "\t" + major + "\t" + college + "\t" + dateOfBirth + "\t" + phone + "\t" + admissionYear);
            }
    };

    /**
     * Class representing a record of Enrollment in the DB
     *
     */
    public static class Enrollment {

            /**
             * The 5 attributes of a record
             *
             */
            public String sid;
            public String cid;
            public long year;
            public long term;
            public String grade;

            /**
             * Constructor of the Enrollment Class.
             *
             */
            public Enrollment() {
            }

            /**
             * Print out all the members for debug.
             *
             */
            public String toString() {
                    return (sid + "\t" + cid + "\t" + year + "\t" + term + "\t" + grade );
            }
    };



    public boolean start() {
        if(init()){
                 if(read_courses()
                 && read_TermCourses()
                 && read_prerequisites()
                 && read_students()
                && read_enrollments()){
                     return true;
                 }else{
                     return false;
                 }
                     
        }else{
            return false;
        }
                
    }

     public boolean init(){

                try {
                    Class.forName("oracle.jdbc.driver.OracleDriver"); //Or any other driver
                }catch(Exception x){
                    System.out.println( "Unable to load the driver class! "+x.getMessage() );
                    return false;
                }

                try{
                    con = DriverManager.getConnection("jdbc:oracle:thin:@db11.cse.cuhk.edu.hk:1521:db11", "b018", "lrtbragg" );
                }catch(Exception x){
                    System.out.println( "Fail to connect to the database!! "+x.getMessage() );
                    return false;
                }

             /*   try{
                    stmt = con.createStatement();

                }catch (Exception x){
                      System.out.println( "Fail to create Statement!!" );
                     return false;
                }*/
                return true;
            }

	/**
	 * Method to read and insert all records of courses.
	 *
	 */
    private boolean read_courses() {
            Course course = new Course();
            String[] ss = new String[4];
            try {
                    String dir=System.getProperty("user.dir");
                    FileReader fr = new FileReader(dir+"/src/csci3170/resources/courses.txt");
                    BufferedReader br = new BufferedReader(fr);
                    String line = br.readLine();
                    while (line != null) {
                            if(!line.startsWith("#")){	// skip comments
                                    ss = line.split("\t", 4); // split s by the seperator "\t"
                                    course.id = ss[0];
                                    course.name = ss[1];
                                    course.credit = Integer.parseInt(ss[2]);
                                    course.department = ss[3];
                                    // TODO: Insert data into DB... query = "CREATE TABLE course ( CourseID char(8),CourseName char(50),Credit int, Department char(3), PRIMARY KEY (CourseID ) ) ";
                                  //  String query = "INSERT INTO course (CourseID,CourseName,Credit,Department) VALUES ('"+course.id+ "','"+course.name+ "',"+course.credit+ ",'"+course.department+ "')";
                                 try{
                                    pstmt = con.prepareStatement("INSERT INTO course (CourseID,CourseName,Credit,Department) VALUES (?,?,?,?)");
                                    pstmt.setString(1, course.id);
                                    pstmt.setString(2, course.name);
                                    pstmt.setLong(3, course.credit);
                                    pstmt.setString(4, course.department);
                                    }catch (Exception x){
                                      System.out.println( "Fail to create Statement!!" );
                                      return false;
                                }

                                    try {
                                        //stmt.executeUpdate(query);
                                        pstmt.executeUpdate();

                                    }catch (Exception e){
                                        System.out.println("Insert fail: "+e.toString());
                                      //  e.printStackTrace();
                                        return false;
                                    }

                            }
                            line = br.readLine();
                    }
                    br.close();
                    fr.close();
            } catch (IOException e) {
                    System.out.println("IO Error:" + e.getMessage());
                    return false;
            } catch (NumberFormatException e) {
                    System.out.println("Number Format Error:" + e.getMessage());
                    return false;
            }
            return true;
    }

    /**
     * Method to read and insert all records of TermCourses.
     *
     */
    private boolean read_TermCourses() {
            TermCourse course = new TermCourse();
            String[] ss = new String[6];
            try {
                     String dir=System.getProperty("user.dir");
                    FileReader fr = new FileReader(dir+"/src/csci3170/resources/termCourses.txt");

                    BufferedReader br = new BufferedReader(fr);
                    String line = br.readLine();
                    while (line != null) {
                            if(!line.startsWith("#")){	// skip comments
                                    ss = line.split("\t", 6); // split s by the seperator "\t"
                                    course.id = ss[0];
                                    course.year = Integer.parseInt(ss[1]);
                                    course.term = Integer.parseInt(ss[2]);
                                    course.prof = ss[3];
                                    course.quota = Integer.parseInt(ss[4]);
                                    course.numberOfEnrolledStudent = Integer.parseInt(ss[5]);
                                    // TODO: Insert course into DB...
                                     /*   String TermCourse =  "CREATE TABLE TermCourse ( "
                                        + " CourseID char(8),"
                                         + "Year int,"
                                         + "Term int, "
                                         + "Professor char(50), "
                                         + "Quota int,"
                                         + "NumberOfEnrolledStudent int,"
                                         + "PRIMARY KEY (CourseID,Year,Term) "
                                         + "FOREIGN KEY (CourseID) REFERENCES course) ";       */

                                   //  String query = "INSERT INTO termcourse (CourseID,Year,Term,Professor,Quota,NumberOfEnrolledStudent) "
                                   //          + "VALUES ('"+course.id+ "',"+course.year+ ","+course.term+ ",'"+course.prof+ "',"+course.quota+ ","+course.numberOfEnrolledStudent+ ")";

                             try{
                                    pstmt = con.prepareStatement("INSERT INTO termcourse (CourseID,Year,Term,Professor,Quota,NumberOfEnrolledStudent) VALUES (?,?,?,?,?,?)");
                                    pstmt.setString(1, course.id);
                                    pstmt.setLong(2, course.year);
                                    pstmt.setLong(3, course.term);
                                    pstmt.setString(4, course.prof);
                                    pstmt.setLong(5, course.quota);
                                    pstmt.setLong(6, course.numberOfEnrolledStudent);
                                    }catch (Exception x){
                                      System.out.println( "Fail to create Statement!! " + x.toString() );
                                      return false;
                                }


                                    try {
                                       // stmt.executeUpdate(query);
                                       pstmt.executeUpdate();
                                    }catch (Exception e){
                                        System.out.println("Insert fail: "+e);
                                      //  e.printStackTrace();
                                        return false;
                                    }

                            }
                            line = br.readLine();
                    }
                    br.close();
                    fr.close();
            } catch (IOException e) {
                    System.out.println("IO Error:" + e.getMessage());
                    return false;
            } catch (NumberFormatException e) {
                    System.out.println("Number Format Error:" + e.getMessage());
                    return false;
            }

            return true;
    }

        
	/**
	 * Method to read and insert all records of student.
	 *
	 */
    private boolean read_students() {
        Student student = new Student();
        String[] ss = new String[9];
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        try {
             String dir=System.getProperty("user.dir");
            FileReader fr = new FileReader(dir+"/src/csci3170/resources/students.txt");
            BufferedReader br = new BufferedReader(fr);
            String line = br.readLine();
            while (line != null) {
				if(!line.startsWith("#")){	// skip comments
					ss = line.split("\t", 9); // split s by the seperator "\t"

					student.id = ss[0];
					student.name = ss[1];
					student.gender = ss[2];
					student.address = ss[3];
					student.major = ss[4];
					student.college = ss[5];
					student.dateOfBirth = (df.parse(ss[6])).getTime();// parse ss[4] into a Date object, which is then converted into milliseconds using getTime()
					java.sql.Date sqlDate = new java.sql.Date(student.dateOfBirth);
                                        student.phone = ss[7];
					student.admissionYear = Integer.parseInt(ss[8]);



					// TODO: Insert student into DB...
                                     /*    String student = "CREATE TABLE student ( "
                                        + " StudentID char(10),"
                                         + "Name char(50),"
                                         + "Gender char(1), "
                                         + "Address char(100), "
                                         + "Major char(3),"
                                         + "College char(50),"
                                         + "DateOfBirth date,"
                                         + "Phone char(20),"
                                         + "AdmissionYear int,"
                                         + "PRIMARY KEY (StudentID ) ) ";   */
                                         //String query = "INSERT INTO student (StudentID,Name,Gender,Address,Major,College,DateOfBirth,Phone,AdmissionYear) "

                                   /*       String query = "INSERT INTO student (StudentID,Name,Gender,Major,College,Phone,AdmissionYear) "
                                                 + "VALUES ("
                                                 + "'"+student.id + "',"
                                                 + "'"+student.name+ "',"
                                                 + "'"+student.gender+ "',"
                                               //  + "'"+student.address+ "',"  //need to handlesome special case such as: abc's
                                                 + "'"+student.major+ "',"
                                                 + "'"+student.college+ "',"

                                                 //+ "'"+student.dateOfBirth+"'," //cannot insert into the table as type of date
                                                 + "'"+student.phone+"',"
                                                 + ""+student.admissionYear+")";*/
                                       //     String query = "INSERT INTO student (StudentID,Name,Gender,Address,Major,College,DateOfBirth,Phone,AdmissionYear) VALUES ('"+student.id + "','"+student.name+ "','"+student.gender+ "','"+student.address+ "','"+student.major+ "','"+student.college+ "','"+student.dateOfBirth+"','"+student.phone+"',"+student.admissionYear+")";
                                        
                                      try{
                                        pstmt = con.prepareStatement("INSERT INTO student (StudentID,Name,Gender,Address,Major,College,DateOfBirth,Phone,AdmissionYear) VALUES (?,?,?,?,?,?,?,?,?)");
                                        pstmt.setString(1, student.id);
                                        pstmt.setString(2, student.name);
                                        pstmt.setString(3, student.gender);
                                        pstmt.setString(4, student.address);
                                        pstmt.setString(5, student.major);
                                        pstmt.setString(6, student.college);
                                        pstmt.setDate(7, sqlDate);
                                        pstmt.setString(8, student.phone);
                                        pstmt.setLong(9, student.admissionYear);
                                        }catch (Exception x){
                                          System.out.println( "Fail to create Statement!!" );
                                          return false;
                                    }

                                        try {
                                           
                                           // stmt.executeUpdate(query);
                                           pstmt.executeUpdate();
                                        }catch (Exception e){
                                            System.out.println("Insert fail: "+"\n"+line);
                                          //  e.printStackTrace();
                                            return false;
                                        }

				}
                line = br.readLine();
            }
            br.close();
            fr.close();
        } catch (IOException e) {
            System.out.println("IO Error:" + e.getMessage());
            return false;
        } catch (ParseException e) {
            System.out.println("Parse Error:" + e.getMessage());
            return false;
        } catch (NumberFormatException e){
			System.out.println("Number Format Error:" + e.getMessage());
        }
                return true;
    }

	/**
	 * Method to read and insert all records of prerequisites.
	 *
	 */
    private boolean read_prerequisites() {
        Prerequisite prerequisite = new Prerequisite();
        String[] ss = new String[2];
        try {
            String dir=System.getProperty("user.dir");
            FileReader fr = new FileReader(dir+"/src/csci3170/resources/prerequisites.txt");
            BufferedReader br = new BufferedReader(fr);
            String line = br.readLine();
            while (line != null) {
				if(!line.startsWith("#")){	// skip comments
					ss = line.split("\t", 2); // split s by the seperator "\t"
					prerequisite.cid = ss[0];
					prerequisite.pid = ss[1];
					// TODO: Insert enrollment into DB...
                                          try{
                                        pstmt = con.prepareStatement("INSERT INTO Prerequisite (CourseID,preCourseID) VALUES (?,?)");
                                        pstmt.setString(1, prerequisite.cid);
                                        pstmt.setString(2, prerequisite.pid);

                                        }catch (Exception x){
                                          System.out.println( "Fail to create Statement!! " + x.toString() );
                                          return false;
                                    }


                                        try {
                                           // stmt.executeUpdate(query);
                                           pstmt.executeUpdate();
                                        }catch (Exception e){
                                            System.out.println("Insert fail: "+e);
                                          //  e.printStackTrace();
                                            return false;
                                        }

				}
                line = br.readLine();
            }
            br.close();
            fr.close();
        } catch (IOException e) {
            System.out.println("IO Error:" + e.getMessage());
			return false;
        } catch (NumberFormatException e) {
            System.out.println("Number format Error:" + e.getMessage());
			return false;
        }

        return true;
    }

	/**
	 * Method to read and insert all records of enrollments.
	 *
	 */
    private boolean read_enrollments() {
        Enrollment enrollment = new Enrollment();
        String[] ss = new String[5];
        try {
            String dir=System.getProperty("user.dir");
            FileReader fr = new FileReader(dir+"/src/csci3170/resources/enrollments.txt");
            BufferedReader br = new BufferedReader(fr);
            String line = br.readLine();
            while (line != null) {
				if(!line.startsWith("#")){	// skip comments
					ss = line.split("\t", 5); // split s by the seperator "\t"
					enrollment.sid = ss[0];
					enrollment.cid = ss[1];
					enrollment.year = Integer.parseInt(ss[2]);
					enrollment.term = Integer.parseInt(ss[3]);
					if(ss[4].compareTo("null") == 0)
						enrollment.grade = null;
					else
						enrollment.grade = ss[4];
					// TODO: Insert enrollment into DB...
                                         try{
                                        pstmt = con.prepareStatement("INSERT INTO Enrollment (CourseID,Year,Term,StudentID,Grade) VALUES (?,?,?,?,?)");
                                        pstmt.setString(1, enrollment.cid);
                                        pstmt.setLong(2, enrollment.year);
                                        pstmt.setLong(3, enrollment.term);
                                        pstmt.setString(4, enrollment.sid);
                                        pstmt.setString(5, enrollment.grade);
                                        }catch (Exception x){
                                          System.out.println( "Fail to create Statement!! " + x.toString() );
                                          return false;
                                    }

                                        try {
                                           // stmt.executeUpdate(query);
                                           pstmt.executeUpdate();
                                        }catch (Exception e){
                                            System.out.println("Insert fail: "+e);
                                          //  e.printStackTrace();
                                            return false;
                                        }
				}
                line = br.readLine();
            }
            br.close();
            fr.close();
        } catch (IOException e) {
            System.out.println("IO Error:" + e.getMessage());
			return false;
        } catch (NumberFormatException e) {
            System.out.println("Number format Error:" + e.getMessage());
			return false;
        }
        return true;
    }
};